﻿using System.Data;
using System.Data.SqlClient;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class ServiceRelateDao
    {
        private readonly SqlConnection _sqlConnection;
        public ServiceRelateDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        #region Constant

        private const string SqlSelect = @"SELECT [Id],[ServiceOrgId]
                                      ,[ServiceID]
                                      ,[ServiceOrgParentId],[IsActive]
                                  FROM [ServiceRelate] WHERE [ServiceOrgParentId] = @ServiceOrgParentId AND [ServiceId] = @ServiceId";
        private const string SqlInsert = @"INSERT INTO [ServiceRelate]
                                               ([ServiceId]
                                                ,[ServiceOrgId]
                                                ,[ServiceOrgParentId]
                                                ,[IsActive])
                                         VALUES
                                               (@ServiceId, 
                                               @ServiceOrgId, 
                                               @ServiceOrgParentId
                                               ,@IsActive)";
        private const string SqlUpdate = @"UPDATE [ServiceRelate]
                                                   SET  [IsActive] = @IsActive
                                                 WHERE [ServiceOrgParentId] = @ServiceOrgParentId AND [ServiceOrgId] = @ServiceOrgId AND [ServiceID] = @ServiceID ";
        private const string SqlDelete = @"DELETE FROM  [ServiceRelate]  WHERE [ServiceOrgParentId] = @ServiceOrgParentId AND [ServiceOrgId] = @ServiceOrgId AND[ServiceID] = @ServiceID";
        private const string SqlCheckExist = @"SELECT Count([Id])
                                  FROM [ServiceRelate] WHERE [ServiceOrgParentId] = @ServiceOrgParentId AND [ServiceOrgId] = @ServiceOrgId AND [ServiceID] = @ServiceID";
        #endregion

        /// <summary>
        /// Selects the specified service org parent id.
        /// </summary>
        /// <param name="serviceOrgParentId">The service org parent id.</param>
        /// <param name="serviceId">The service id.</param>
        /// <returns></returns>
        public DataTable Select(int serviceOrgParentId, int serviceId)
        {
            var param = new SqlParameter[2];
             param[0] = new SqlParameter("@ServiceOrgParentId", SqlDbType.Int) { Value = serviceOrgParentId };
             param[1] = new SqlParameter("@ServiceId", SqlDbType.Int) { Value = serviceId };
             return SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlSelect, param).Tables[0];
        }
        /// <summary>
        /// Inserts the specified service relate.
        /// </summary>
        /// <param name="serviceRelate">The service relate.</param>
        /// <returns></returns>
        public  bool Insert(ServiceRelate serviceRelate)
        {
            var param = new SqlParameter[4];
            param[0] = new SqlParameter("@ServiceOrgParentId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgParentId };
            param[1] = new SqlParameter("@ServiceOrgId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceRelate.ServiceId };
            param[3] = new SqlParameter("@IsActive", SqlDbType.Int) { Value = serviceRelate.IsActive };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlInsert, param) != 0;
        }
        /// <summary>
        /// Updates the specified service relate.
        /// </summary>
        /// <param name="serviceRelate">The service relate.</param>
        /// <returns></returns>
        public  bool Update(ServiceRelate serviceRelate)
        {
            var param = new SqlParameter[4];
            param[0] = new SqlParameter("@ServiceOrgParentId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgParentId };
            param[1] = new SqlParameter("@ServiceOrgId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceRelate.ServiceId };
            param[3] = new SqlParameter("@IsActive", SqlDbType.Int) { Value = serviceRelate.IsActive };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlUpdate, param) != 0;
        }
        /// <summary>
        /// Deletes the specified service relate.
        /// </summary>
        /// <param name="serviceRelate">The service relate.</param>
        /// <returns></returns>
        public  bool Delete(ServiceRelate serviceRelate)
        {
            var param = new SqlParameter[3];
            param[0] = new SqlParameter("@ServiceOrgParentId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgParentId };
            param[1] = new SqlParameter("@ServiceOrgId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceRelate.ServiceId };
            return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlDelete, param) != 0;
        }
        /// <summary>
        /// Determines whether the specified service relate is exist.
        /// </summary>
        /// <param name="serviceRelate">The service relate.</param>
        /// <returns>
        ///   <c>true</c> if the specified service relate is exist; otherwise, <c>false</c>.
        /// </returns>
        public bool IsExist(ServiceRelate serviceRelate)
        {
            var param = new SqlParameter[3];
            param[0] = new SqlParameter("@ServiceOrgParentId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgParentId };
            param[1] = new SqlParameter("@ServiceOrgId", SqlDbType.Int) { Value = serviceRelate.ServiceOrgId };
            param[2] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceRelate.ServiceId };
            return SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, SqlCheckExist, param).ConvertToDataTable().Rows.Count > 0;
        }
    }
}
